<?php
// ===============================================================
// Zend_Db Tutorial Example Code
// Part 1: Selecting Data
// ---------------------------------------------------------------
// Written by Alan Wagstaff
// Donated to the Public Domain
// ===============================================================

// ----------------
// Setup
// ----------------
set_include_path('.'
	. PATH_SEPARATOR . '../../library'
	. PATH_SEPARATOR . get_include_path()
);

include 'Zend/Db.php';

// ----------------
// Connecting to the Database
// ----------------
$dbConfig = array(
	'host'		=> 'xxxHOSTxxx',
	'username'	=> 'xxxUSERxxx',
	'password'	=> 'xxxPASSxxx',
	'dbname'	=> 'xxxDBxxx'
);

$db = Zend_Db::factory('Pdo_Mysql', $dbConfig);

// ----------------
// Using Select
// ----------------
$query = $db->select();
$query->from('articles', array('articleid', 'title'));
// Query: SELECT `articles`.`articleid`, `articles`.`title`
//        FROM `articles`

$result = $db->fetchAll($query);

echo '<h1>All articles with default sort order</h1>';
foreach ($result as $row)
{
	echo 'Article #' . $row['articleid'] . ' - ' . $row['title'] . '<br />';
}

echo '<hr />';

// ----------------
// Using Order
// ----------------
$query = $db->select();
$query->from('articles', array('articleid', 'title'));
$query->order('title ASC');
// Query: SELECT `articles`.`articleid`, `articles`.`title` 
//        FROM `articles` 
//        ORDER BY `title` ASC

$result = $db->fetchAll($query);

echo '<h1>All articles sorted by title (A-Z)</h1>';
foreach ($result as $row)
{
	echo 'Article #' . $row['articleid'] . ' - ' . $row['title'] . '<br />';
}

echo '<hr />';

// ----------------
// Using Joins
// ----------------
$query = $db->select();
$query->from(array('a' => 'articles'),
			 array('articleid', 'title', 'created'));
$query->join(array('m' => 'authors'), 'a.authorid = m.authorid');
$query->order('created DESC');
// Query: SELECT `a`.`articleid`, `a`.`title`, `a`.`created`, `m`.* 
// 		  FROM `articles` AS `a` 
// 		  INNER JOIN `authors` AS `m` 
// 		  ON a.authorid = m.authorid 
// 		  ORDER BY `created` DESC

$result = $db->fetchAll($query);

echo '<h1>All articles with author and creation date</h1>';
foreach ($result as $row)
{
	echo '<a href="view.php?id=' . $row['articleid'] . '">' . $row['title'] . '</a><br />';
	echo 'Written by <a href="mailto:' . $row['email'] . '">' . $row['name'] . '</a><br />';
	echo 'Created on ' . date('jS F o', $row['created']) . '<br /><br />';            
}

echo '<hr />';

// ----------------
// Using Where
// ----------------
$author = 'Alan';

$query = $db->select();
$query->from(array('a' => 'articles'),
			 array('articleid', 'title', 'content', 'created'));
$query->join(array('m' => 'authors'), 'a.authorid = m.authorid', array());
$query->order('title ASC');
$query->where('m.name = ?', $author);
$query->where('a.created > 1203427591');
// Query: SELECT `a`.`articleid`, `a`.`title`, `a`.`content`
// 		  FROM `articles` AS `a` 
//		  INNER JOIN `authors` AS `m` 
//		  ON a.authorid = m.authorid 
//		  WHERE (m.name = 'Alan') AND (a.created > 1204205135) 
//		  ORDER BY `title` ASC

$result = $db->fetchAll($query);

echo '<h1>All articles written by ' . $author . ' and dated after ' . date('jS F o', 1203427590) . '</h1>';
foreach ($result as $row)
{
	echo 'Article #' . $row['articleid'] . ': ' . $row['title'] . ' - ' . date('jS F o', $row['created']) . '<br />';
	echo nl2br($row['content']) . '<br /><br />';
}

echo '<hr />';

// ----------------
// Using Group
// ----------------
$query = $db->select();
$query->from(array('a' => 'articles'), array('article_count' => 'COUNT(a.articleid)'));
$query->join(array('m' => 'authors'), 'a.authorid = m.authorid', array('m.name'));
$query->order('article_count DESC');
$query->group('a.authorid');
// Query: SELECT COUNT(a.articleid) AS `article_count`, `m`.`name` 
//		  FROM `articles` AS `a` 
//        INNER JOIN `authors` AS `m` ON a.authorid = m.authorid 
//        GROUP BY `a`.`authorid` 
//        ORDER BY `article_count` DESC

$result = $db->fetchAll($query);

echo '<h1>Authors and Article Counts</h1>';
foreach ($result as $row)
{
	echo $row['name'] . " has published " . $row['article_count'] . ' article(s)<br />';
}

echo '<hr />';

// ----------------
// Using Limit
// ----------------
$query = $db->select();
$query->from('articles', array('articleid', 'title'));
$query->limit(4);
// Query: SELECT `articles`.`articleid`, `articles`.`title` 
//        FROM `articles` 
//        LIMIT 4

$result = $db->fetchAll($query);

echo '<h1>Articles 1-4</h1>';
foreach ($result as $row)
{
	echo 'Article #' . $row['articleid'] . ': ' . $row['title'] . '<br />';
}

echo '<hr />';

// ----------------
// Using Limit Offset
// ----------------
$query = $db->select();
$query->from('articles', array('articleid', 'title'));
$query->limit(3, 2);
// Query: SELECT `articles`.`articleid`, `articles`.`title` 
//        FROM `articles` 
//        LIMIT 3 
//        OFFSET 2

$result = $db->fetchAll($query);

echo '<h1>Articles 3-5</h2>';
foreach ($result as $row)
{
	echo 'Article #' . $row['articleid'] . ': ' . $row['title'] . '<br />';
}

echo '<hr />';

// ----------------
// Fetch a single row
// ----------------
$query = $db->select();
$query->from('articles');
$query->where('authorid = 1');
$query->order('created DESC');
$query->limit(1);
// Query: SELECT `articles`.* 
//        FROM `articles` 
//        WHERE (authorid = 1) 
//        ORDER BY `created` DESC 
//        LIMIT 1

$result = $db->fetchRow($query);

echo '<h1>Newest Article Written by Alan</h1>';
echo $result['title'] . ' (' . date('jS F o', $result['created']) . ')<br />';
